package com.rx.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.rx.common.DBUtils;
import com.rx.entity.Role;

public class RoleDaoImpl implements RoleDaoI<Role> {

	@Override
	public boolean insert(Role role) throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "INSERT INTO role (roleName,remark) VALUES(?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, role.getRoleName());
		ps.setString(2, role.getRemark());
		if (ps.executeUpdate() > 0) {
			return true;
		}
		return false;
	}

	@Override
	public boolean update(Role role) throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "UPDATE role SET roleName=?,remark=? WHERE id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, role.getRoleName());
		ps.setString(2, role.getRemark());
		ps.setInt(3, role.getId());
		if (ps.executeUpdate() > 0) {
			return true;
		}

		return false;
	}

	@Override
	public boolean delete(int id) throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "DELETE FROM role WHERE id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		if (ps.executeUpdate() > 0) {
			return true;
		}
		return false;
	}

	@Override
	public List<Role> selectAll() throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "SELECT * FROM role";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		List<Role> list = new ArrayList<>();
		while (rs.next()) {
			Role role = new Role();
			role.setId(rs.getInt(1));
			role.setRoleName(rs.getString(2));
			role.setRemark(rs.getString(3));
			list.add(role);
		}
		return list;
	}

	@Override
	public List<Role> selectWhere(String whereSql) throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "SELECT id,roleName,remark FROM role WHERE 1=1 " + whereSql;
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		List<Role> list = new ArrayList<>();
		while (rs.next()) {
			Role role = new Role();
			role.setId(rs.getInt(1));
			role.setRoleName(rs.getString(2));
			role.setRemark(rs.getString(3));
			list.add(role);
		}
		DBUtils.close(conn, ps, rs);
		return list;
	}

	@Override
	public Map<String, Object> selectPage(String whereSql, int currentPage, int pageSize) throws Exception {
		Connection conn = DBUtils.getConnection();
		int index = (currentPage - 1) * pageSize;
		StringBuffer sb=new StringBuffer();
		sb.append("SELECT id,roleName,remark FROM role WHERE 1=1 ");
		sb.append(whereSql);
		sb.append(" ORDER BY id desc ");
		sb.append( " LIMIT " + index + "," + pageSize);
//		String sql = "SELECT id,roleName,remark FROM role WHERE 1=1 " + whereSql + " LIMIT " + index + "," + pageSize;
		PreparedStatement ps0 = conn.prepareStatement(sb.toString());
		ResultSet rs0 = ps0.executeQuery();
		List<Role> list = new ArrayList<>();
		while (rs0.next()) {
			Role role = new Role();
			role.setId(rs0.getInt(1));
			role.setRoleName(rs0.getString(2));
			role.setRemark(rs0.getString(3));
			list.add(role);
		}

		String sql = "SELECT count(*) FROM role  WHERE 1=1 " + whereSql;
		PreparedStatement ps1 = conn.prepareStatement(sql);
		ResultSet rs1 = ps1.executeQuery();
		int count = 0;
		int pageNum=0;
		if (rs1.next()) {
			count = rs1.getInt(1);
		}
		
		Map<String,Object> map=new HashMap<>();
		
		if(count%pageSize==0) {
			pageNum=count/pageSize;
		}else {
			pageNum=count/pageSize+1;
		}
		
		map.put("list", list);
		map.put("count", count);
		map.put("pageNum", pageNum);
		//关闭连接
		DBUtils.close(conn, ps0, rs0);
		DBUtils.close(conn, ps1, rs1);
		return map;
	}
}
